# encoding=utf-8
from mysql.connector.cursor import MySQLCursorBufferedDict
from mysql.connector.pooling import MySQLConnectionPool

# from conf.config import MYSQL_CONFIG

from conf.config import POOL  # 导入我们创建的数据库连接池包
import pymysql


# __pool = None


# 打开连接


# sql = "insert into users(name,age) VALUES (%s, %s)"

# insert(sql,("mjj",9))

# sql = "select * from users where name=%s and age=%s"  # sql语句
#
# print(fetch_one(sql,("mjj",9))) # 打印结果


# def get_mysql_connection_pool():
# global __pool
# if __pool:
#     return __pool
# __pool = MySQLConnectionPool(**MYSQL_CONFIG)
# # __pool = MySQLConnectionPool(20,**MYSQL_CONFIG)
# return __pool
# create_conn()

# 获取连接
def create_conn():
    conn = POOL.connection()
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    return conn, cursor


# 关闭连接
def close_conn(conn, cursor):
    cursor.close()
    conn.close()


class MySQLClient:
    # def __init__(self):
    #     self.cp = get_mysql_connection_pool()

    def __init__(self):
        pass

    # 插入一条数据
    # def insert(sql, args):
    #     conn, cursor = create_conn()
    #     res = cursor.execute(sql, args)
    #     conn.commit()
    #     close_conn(conn, cursor)
    #     return res

    # 查询一条数据
    def fetchone(self, sql):
        conn, cursor = create_conn()
        cursor.execute(sql)
        res = cursor.fetchone()
        close_conn(conn, cursor)
        return res

    # 查询所有数据
    def fetchall(self, sql):
        conn, cursor = create_conn()
        cursor.execute(sql)
        res = cursor.fetchall()
        close_conn(conn, cursor)
        return res

    # def fetchall(self, sql):
    #     con = self.cp.get_connection()
    #     cur = con.cursor(cursor_class=MySQLCursorBufferedDict)
    #     cur.execute(sql)
    #     r = cur.fetchall()
    #     cur.close()
    #     con.close()
    #     return r
    #
    # def fetchone(self, sql):
    #     con = self.cp.get_connection()
    #     cur = con.cursor(cursor_class=MySQLCursorBufferedDict)
    #     cur.execute(sql)
    #     r = cur.fetchone()
    #     cur.close()
    #     con.close()
    #     return r
    #
    def execute(self, sql):
        # con = self.cp.get_connection()
        # cur = con.cursor(cursor_class=MySQLCursorBufferedDict)
        conn, cur = create_conn()
        # cursor.execute(sql)
        cur.execute('start transaction;')
        cur.execute(sql)
        cur.execute('commit;')
        # cur.close()
        # con.close()
        close_conn(conn, cur)


cli = MySQLClient()

if __name__ == '__main__':
    config = {
        'user': 'root',
        'password': '123456',
        'host': 'localhost',
        'db': 'nb',
        'port': 3306
    }
    res = cli.fetchall('SELECT * FROM demo')
    print res
    res = cli.fetchone('SELECT id, city_name FROM pon_traffic_statistics_csv LIMIT 1')
    print res
